﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace wd_37_combobox_v1
{
    public class DBOpt
    {
        public static void CreateDB(string file)
        {
            try
            {
                SQLiteConnection.CreateFile(file);
                return;
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static void CreateTable1(string db1, string table1)
        {
            try
            {
                SQLiteConnection conn = new SQLiteConnection("data source=" + db1);
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = conn;
                cmd.CommandText = "CREATE TABLE " + table1 + "(id integer, city Text)";
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static void CreateTable2(string db1, string table1)
        {
            try
            {
                SQLiteConnection conn = new SQLiteConnection("data source=" + db1);
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = conn;
                cmd.CommandText = "CREATE TABLE " + table1 + "(tel integer primary key asc)";
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static void DeleteTable1(string db1, string table1)
        {
            try
            {
                SQLiteConnection conn = new SQLiteConnection("data source=" + db1);
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = conn;
                cmd.CommandText = "DROP TABLE " + table1;
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static void Insert1(string[] keys, string[] values, string _tb_name, string db1)
        {
            string keys_string = "(" + keys[0] +")";
            string values_string = "('" + values[0] + "')";

            string sql1 = string.Format("INSERT INTO "+_tb_name+" {0} VALUES {1}", keys_string, values_string);

            try
            {
                SQLiteConnection conn = new SQLiteConnection("data source=" + db1);
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand(sql1, conn);
                int rows = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static void QueryData1(string _tb, string _db1)
        {
            try
            {
                SQLiteConnection conn = new SQLiteConnection("data source=" + _db1);
                conn.Open();

                //SQLiteDataAdapter adp1 = new SQLiteDataAdapter("select * from " +_tb, conn);
                //DataTable dt = new DataTable();
                //adp1.Fill(dt);

                SQLiteCommand cmd = new SQLiteCommand("select * from " + _tb, conn);
                SQLiteDataReader reader = cmd.ExecuteReader();

                List<string> list1 = new List<string>();
                while (reader.Read())
                {
                    list1.Add(reader["tel"].ToString());
                }

                conn.Close();
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


        public static Tuple<List<string>> QueryData2(string _tb, string _db1)
        {
            try
            {
                SQLiteConnection conn = new SQLiteConnection("data source=" + _db1);
                conn.Open();

                //SQLiteDataAdapter adp1 = new SQLiteDataAdapter("select * from " +_tb, conn);
                //DataTable dt = new DataTable();
                //adp1.Fill(dt);

                SQLiteCommand cmd = new SQLiteCommand("select * from " + _tb, conn);
                SQLiteDataReader reader = cmd.ExecuteReader();

                List<string> list1 = new List<string>();
                while (reader.Read())
                {
                    list1.Add(reader["tel"].ToString());
                }

                conn.Close();
                return Tuple.Create(list1);
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

    }
}
